﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Globalization;
using System.Linq;

using BLL.Services.Books.Listing;

namespace BLL.Services.Books
{
    public class BooksCollection
    {
        private readonly string _connectionString;

        public BooksCollection(string connectionString)
        {
            _connectionString = connectionString;
        }

        public void Create(Book model)
        {
            model.DateOfPublish = DateTime.Now;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Books.Insert(
                    conn, model.DateOfPublish, model.ShortContent, model.Name, model.Avatar, model.FileName, model.UserId);

                var authors = new DataTable("list_author");
                authors.Columns.Add("AuthorId", typeof(int));
                foreach (var author in model.AuthorIds)
                    authors.Rows.Add(author);
                var authorParam = new SqlParameter
                {
                    ParameterName = "@authors",
                    SqlDbType = SqlDbType.Structured,
                    Value = authors
                };
                cmd.Parameters.Add(authorParam);

                var categories = new DataTable("list_category");
                categories.Columns.Add("CategoryId", typeof(int));
                foreach (var category in model.CategoryIds)
                    categories.Rows.Add(category);
                var categoriesParam = new SqlParameter
                {
                    ParameterName = "@categories",
                    SqlDbType = SqlDbType.Structured,
                    Value = categories
                };
                cmd.Parameters.Add(categoriesParam);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (reader.HasRows)
                        {
                            model = GetBook(reader);
                        }
                    }
                }
            }
        }

        public void Save(Book model)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Books.Update(
                    conn, model.BookId, model.DateOfPublish, model.ShortContent, model.Name, model.Avatar, model.FileName, model.UserId);

                var authors = new DataTable("list_author");
                authors.Columns.Add("AuthorId", typeof(int));
                foreach (var author in model.AuthorIds)
                    authors.Rows.Add(author);
                var authorParam = new SqlParameter
                {
                    ParameterName = "@authors",
                    SqlDbType = SqlDbType.Structured,
                    Value = authors
                };
                cmd.Parameters.Add(authorParam);

                var categories = new DataTable("list_category");
                categories.Columns.Add("CategoryId", typeof(int));
                foreach (var category in model.CategoryIds)
                    categories.Rows.Add(category);
                var categoriesParam = new SqlParameter
                {
                    ParameterName = "@categories",
                    SqlDbType = SqlDbType.Structured,
                    Value = categories
                };
                cmd.Parameters.Add(categoriesParam);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        model = GetBook(reader);
                    }
                }
            }
        }

        /// <summary>
        /// Method checks if there are no books with the same name and authors in DB
        /// is SqlReader returns Id!= bookId: book with the same name and authors has already been created
        /// if SqlReader returns 2 values - method returns false: there are 2 books with same name and authors
        /// </summary>
        /// <param name="bookId">The book id.</param>
        /// <param name="name">The name.</param>
        /// <param name="authors">The authors.</param>
        public bool ValidateBook(int bookId, string name, int[] authors)
        {
            var result = false;
            using (var conn = new SqlConnection(_connectionString))
            {
                var authorsIdAsString = string.Empty;
                Array.Sort(authors);
                foreach (var author in authors)
                {
                    authorsIdAsString += author.ToString(CultureInfo.InvariantCulture) + ",";
                }

                conn.Open();
                var cmd = StoredProcedures.Books.Validate(conn, bookId, name, authorsIdAsString);

                var isValidParameter = new SqlParameter("@valid", SqlDbType.Bit, 4)
                    { Direction = ParameterDirection.ReturnValue };
                cmd.Parameters.Add(isValidParameter);

                cmd.ExecuteNonQuery();
                result = (int)isValidParameter.Value == 1;
            }
            return result;
        }

        public void Delete(int bookId)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Books.Delete(conn, bookId);

                cmd.ExecuteNonQuery();
            }
        }

        public IEnumerable<BookWithRating> GetBestBooks(int? userId)
        {
            var result = new List<BookWithRating>();
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Books.GetBest(conn, userId ?? 0);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var book = new BookWithRating();
                        book.BookId = reader.GetInt32(0);
                        book.DateOfPublish = reader.GetDateTime(1);
                        book.ShortDescription = reader[2] as string;
                        book.Name = reader[3] as string;
                        book.Avatar = reader[4] as string;
                        book.FileName = reader[5] as string;
                        book.AuthorIds =
                            (reader[6] as string ?? ",").Split(new[] { "," }, StringSplitOptions.RemoveEmptyEntries).
                                Select(x => Convert.ToInt32(x)).ToArray();
                        book.Rating = Convert.ToInt32(reader[7] as string ?? "0");
                        book.IsCanRate = (userId.HasValue && (reader[8] as string) == "0");
                        result.Add(book);
                    }
                }
            }
            return result;
        }

        public BooksListing GetListingBook(int page, int size, string column, int direction, BookFilter filter)
        {
            if (filter == null)
            {
                filter = new BookFilter();
            }
            var result = new BooksListing
            {
                Page = page,
                Size = size,
                Column = column,
                Direction = direction,
                Filter = filter
            };

            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Books.GetListing(
                    conn, page, size, column, direction, filter.Name, filter.AuthorId, filter.CategoryId);

                var totalCount = new SqlParameter("@TotalCount", SqlDbType.Int, 4)
                    { Direction = ParameterDirection.ReturnValue };

                cmd.Parameters.Add(totalCount);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var item = new BooksListing.Book();
                        item.BookId = reader.GetInt32(0);
                        item.Name = reader.GetString(1);
                        item.ShortContent = reader.GetString(2);
                        item.DateOfPublish = reader.GetDateTime(3).ToShortDateString().ToString(CultureInfo.InvariantCulture);
                        item.ImageUrl = reader.GetString(4);

                        var authors = reader[5] as string ?? string.Empty;
                        item.Authors = new List<string>();
                        foreach (var authorName in authors.Split(new[] { "," },
                            StringSplitOptions.RemoveEmptyEntries))
                        {
                            item.Authors.Add(authorName);
                        }

                        var categories = reader[6] as string ?? string.Empty;
                        item.Categories = new List<string>();
                        foreach (var categoryName in categories.Split(new[] { "," },
                            StringSplitOptions.RemoveEmptyEntries))
                        {
                            item.Categories.Add(categoryName);
                        }

                        result.Books.Add(item);
                    }
                }

                result.TotalPages = (int)totalCount.Value % size == 0
                                        ? (int)totalCount.Value / size
                                        : ((int)totalCount.Value / size) + 1;
            }
            return result;
        }




        public Book SelectById(int bookId)
        {
            Book result = null;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Books.SelectById(conn, bookId);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        result = GetBook(reader);
                    }
                }
            }
            return result;
        }

        public IEnumerable<Book> SelectByAuthor(int authorId)
        {
            var result = new List<Book>();
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Books.SelectByAuthor(conn, authorId);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        result.Add(GetBook(reader));
                    }
                }
            }
            return result;
        } 
        public IEnumerable<Book> SelectAll()
        {
            var result = new List<Book>();
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Books.SelectAll(conn);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var book = GetBook(reader);
                        result.Add(book);
                    }
                }
            }
            return result;
        }

        private static Book GetBook(SqlDataReader reader)
        {
            var book = new Book();
            book.BookId = reader.GetInt32(0);
            book.DateOfPublish = reader.GetDateTime(1);
            book.ShortContent = reader[2] as string;
            book.Name = reader[3] as string;
            book.Avatar = reader[4] as string;
            book.FileName = reader[5] as string;
            book.UserId = reader.GetInt32(6);
            book.AuthorIds = (reader[7] as string ?? ",").Split(new[] {","}, StringSplitOptions.RemoveEmptyEntries).Select(x => Convert.ToInt32(x)).ToArray();
            book.CategoryIds = (reader[8] as string ?? ",").Split(new[] {","}, StringSplitOptions.RemoveEmptyEntries).Select(x => Convert.ToInt32(x)).ToArray();
            return book;
        }

        public IEnumerable<BookShortInfoModel> GetSomeBooksByCategory(int categoryId, int userId, int page, int size, string column, int direction)
        {
            var result = new List<BookShortInfoModel>();
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Books.GetBooksByCategory(conn, page, size, column, direction, categoryId, userId);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var item = new BookShortInfoModel
                            {
                                BookId = reader.GetInt32(0),
                                BookTitle = reader.GetString(1),
                                BookImg = reader.GetString(2),
                                ShortContent = reader.GetString(3),
                                FileName = reader.GetString(4),
                                Rating = Convert.ToInt32(reader[5] as string ?? "0"),
                                IsCanRate = userId != 0 && (reader[6] as string) == "0"
                            };
                        result.Add(item);
                    }
                }
            }
            return result;
        }

        public IEnumerable<BookShortInfoModel> GetProfileRatedBooks(string email, int page, int size, string column, int direction, int currentUserId)
        {
            var result = new List<BookShortInfoModel>();
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Books.GetProfileRatedBooksByLoginOrEmail(conn, page, size, column, direction, email, currentUserId);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var item = new BookShortInfoModel
                        {
                            BookId = reader.GetInt32(0),
                            BookTitle = reader.GetString(1),
                            BookImg = reader.GetString(2),
                            ShortContent = reader.GetString(3),
                            FileName = reader.GetString(4),
                            Rating = Convert.ToInt32(reader[5] as string ?? "0"),
                            IsCanRate = currentUserId != 0 && (reader[6] as string) == "0"
                        };
                        result.Add(item);
                    }
                }
            }
            return result;
        }
    }
}